package org.eclipse.epsilon.emc.rdbms.dbConnection;
import java.awt.List;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;

import org.h2.tools.RunScript;

/*************************************************************************************************
 * @author Phillip
 * @param <includes>
 * @Date 28/06/2012
 * @Description This is used to connect to the H2 Embedded Database Engine
 **************************************************************************************************/

public class H2Connection
{
	   private static java.sql.Connection  con = null;
	   private  String url;
	   private  String serverName;    //hostname for the database server
	   private  String databaseName;  //the databasse name that we want to connect to
	   	   
	   public H2Connection(String url, String serverName, String databaseName)
	   {
	  	 this.url=url;
	  	 this.serverName=serverName;
	  	 this.databaseName=databaseName;
	   }
	 	
	   public String geturl()
	   {
	  	return this.url; 
	   }
	   
	   public String getserverName()
	   {
	  	return this.serverName; 
	   }
	   
	   public String getdatabaseName()
	   {
	  	return this.databaseName; 
	   }
	   
	  
	   public void seturl(String url)
	   {
	   	this.url = url;
	   }
	   
	   public void setserverName(String serverName)
	   {
	   	this.serverName = serverName;
	   }
	   
	   public void setdatabaseName(String databaseName)
	   {
	   	this.databaseName = databaseName;
	   }
	   
	    
	   //get the connection URL for the database connection.
	   private String getConnectionUrl()
	   {           
		   return geturl()+getserverName()+"/"+getdatabaseName()+";";
	   }
	   
	   
	   public void db_setup(String fileLocation)
	   {
		   
		   con= this.getConnection();
           if(con!=null)
           {
        	   try 
        	   {
				RunScript.execute(con, new FileReader(fileLocation));
			   } 
        	   catch (FileNotFoundException e) 
        	   {
				// TODO Auto-generated catch block
				e.printStackTrace();
			   } 
        	   catch (SQLException e) 
        	   {
				// TODO Auto-generated catch block
				e.printStackTrace();
			   }	   
	       }
	   }
	   
	   
	   
	   public java.sql.Connection getConnection()
	   {
	  	//This is the driver for the SQL Server 2012.
	        try{
	      	  Class.forName("org.h2.Driver");
	             con = java.sql.DriverManager.getConnection(getConnectionUrl());     	           
	        }
	         catch(Exception e)
	            {
	             e.printStackTrace();
	             System.out.println("Error Trace in getConnection() : " + e.getMessage());
	           }   	 
	        return con;
	    }
	   
	   /*
	        Display the driver properties, database details for test purposes only.
	   */ 
	   public void getDbProperties(){
	        java.sql.DatabaseMetaData dm = null;
	        java.sql.ResultSet rs = null;
	        try{
	             con= this.getConnection();
	             if(con!=null){
	                  dm = con.getMetaData();
	                  System.out.println("Driver Information");
	                  System.out.println("\tDriver Name: "+ dm.getDriverName());
	                  System.out.println("\tDriver Version: "+ dm.getDriverVersion ());
	                  System.out.println("\nDatabase Information ");
	                  System.out.println("\tDatabase Name: "+ dm.getDatabaseProductName());
	                  System.out.println("\tDatabase Version: "+ dm.getDatabaseProductVersion());
	                  System.out.println("Avalilable Catalogs ");
	                  rs = dm.getCatalogs();
	                  while(rs.next()){
	                       System.out.println("\tcatalog: "+ rs.getString(1));
	                  } 
	                  rs.close();
	                  rs = null;
	                  closeConnection();
	             }else System.out.println("Error: There was an issue getting the datbaase property information.");
	        }catch(Exception e){
	             e.printStackTrace();
	        }
	        dm=null;
	   }     
	   
	   
	  
	public ArrayList getSQLTables()
	   {
		ArrayList<String> arrTables = new ArrayList(); 
		int i = 0;							   
		
		java.sql.ResultSet rs = null;       
	       try
	       {
	    	    con= this.getConnection();  
	            if(con!=null)               
	            {
	            	String[] type = new String[1];
	            	type[0] = "TABLE";      
	            	rs = con.getMetaData().getTables(getdatabaseName().toUpperCase(), null, null, type);
	   	           while (rs.next())
	                 {
	   	        	   arrTables.add(rs.getString(3));
	   	        	   i++;
	                 }
					  return arrTables;
	             }
	             else System.out.println("No Database Connection was set");
	        }
	        catch(Exception e)
	        {
	             e.printStackTrace();
	        }
			return null;
	  }      
	           
	  public ArrayList<String> getSQLColumns(String tablename) 
	   {
		  ArrayList<String> arrCols = new ArrayList<String>();
	  	try
	  	{
	  		 con= this.getConnection();
	           if(con!=null)
	           {
	        	      
	        	      DatabaseMetaData dbmd = con.getMetaData();
	        	      ResultSet rs = dbmd.getColumns(getdatabaseName().toUpperCase(), null,tablename,"%");
	        	      
	        	      while (rs.next()) 
	        	      {
	        	            arrCols.add(rs.getString("COLUMN_NAME"));
	        	      }
	        	return arrCols;      
	           } 
	           else System.out.println("Error: There were problems retrieving the column information");
	      }
	  	catch(Exception e)
	      {
	  		System.out.println("Error: There were problems retrieving the column information");
	           e.printStackTrace();
		 }
	      return arrCols;
	   }

		private void closeConnection()
	   {
	        try{
	             if(con!=null)
	                  con.close();
	             con=null;
	        }catch(Exception e){
	             e.printStackTrace();
	        }
	   }
		
		public ArrayList<String> getPrimaryKey(String tablename)
		{
			 
			java.sql.DatabaseMetaData md = null;
		       java.sql.ResultSet rs = null;
		       try
		       {
		            con= this.getConnection();
		            if(con!=null)
		            {
		          	    rs = con.getMetaData().getPrimaryKeys(null, null, tablename);
		          	    ArrayList<String> arrPrimaryKeysName = new ArrayList<String>(); 
		          	    ArrayList<String> arrPrimaryKeysIds = new ArrayList<String>();
		          	    int recordCount = 0;
		          	    
		          	    while (rs.next()) 
		          	   {	
		               String columnName = rs.getString("COLUMN_NAME");
		                arrPrimaryKeysName.add(columnName);
		                recordCount++; 
		              }
		          	    
		          	  if(recordCount==0)
		          	  {
		          		for (int i = 1; i <= getSQLColumns(tablename).size(); i++)
			         	   {
		          			arrPrimaryKeysIds.add(Integer.toString(i));			         	   
			         	   }
			          } 
		          	  else 
		          	  {
		          		for (int i = 0; i < getSQLColumns(tablename).size() ; i++)
		          		{
		          			  if(arrPrimaryKeysName.contains(getSQLColumns(tablename).get(i).toString()))
		          		      { 
		          		    	arrPrimaryKeysIds.add(Integer.toString(i));			         	   	     
		          		      }
		          		      
			         	  }
		          	  }
		          	  return arrPrimaryKeysIds;  
		            }
		                 else System.out.println("Error: No Connection defined");
		        }
		        catch(Exception e)
		        {
		             e.printStackTrace();
		        }
		        return null;
		  }      
			
		public ArrayList<String[]> getAllRows(String tablename) throws SQLException
		{	
			ArrayList <String[]> result = new ArrayList<String[]>(); 
			Statement st = con.createStatement();
			// Turn use of the cursor on. 
			st.setFetchSize(5000);  //Fetch 5000 rows at a time should see a performance benefit may need to tweak to see if we can improve performance more.
			ResultSet rs = st.executeQuery("SELECT * FROM INFORMATION_SCHEMA.".concat(tablename)); 
			int columnCount = rs.getMetaData().getColumnCount();
			while (rs.next()) 
			{ 
				String[] row = new String[columnCount]; 
			    for (int i=0; i <columnCount ; i++) 
			    { 
			       row[i] = rs.getString(i + 1); 
			    } 
			    result.add(row); 
			} 		
			rs.close(); 
 
			return result; 
		}
		
		
		private String generateInsertStatements(String type) throws Exception 
		{
		    ResultSet rs  = con.getMetaData().getColumns(getdatabaseName().toUpperCase(), null, type, null);
		    String columns = "";
		    String values = "";
			String statement ="";
			//System.out.println(statement);
			int i=0;
			while (rs.next()) 
			{
			i++; 
			 if(rs.getMetaData().isAutoIncrement(i)==true) //This seems to always return false....and therefore inserts fail.
			 {
				 //do nothing.
			 }
			//This is a workaround as there is a bug in the JDBC Driver that always means that isAutoIncrement(i)==true always returns false *** MAJOR ISSUE ****
			 else if(rs.getString(4).toString().equals("CUSTOMERID"))
			 { 
			 }
			//We first check if the column can have null values and is writable. 
			 else if(rs.getBoolean(11)==true && rs.getMetaData().isWritable(i)==true) 
			{
			   columns = columns.concat(rs.getString(4)); 
			   values= values.concat("null"); 
			   columns = columns.concat(",");
			   values = values.concat(",");
	             
			}
			//Now we must check that if the column is not nullable what data type the column is so we can insert a valid placeholder for the data.
			else if(rs.getBoolean(11)==false) 				
             {	 
            	//We need to get a default value for the data type of the column
			   columns = columns.concat(rs.getString(4)); 	 
  			   values= values.concat("'").concat(getMappedDataTypeDefault(rs.getString(6).toString()).concat("'"));
  			 columns = columns.concat(",");
			 values = values.concat(",");
             
             } 
             statement = "INSERT INTO ".concat(rs.getString(1)).concat(".").concat(rs.getString(2).toString().concat(".").concat(type)); 
			}
			columns = columns.substring(0, columns.length() - 1);
			values = values.substring(0, values.length() - 1);
			statement = statement.concat("(").concat(columns).concat(")").concat(" VALUES ").concat("(").concat(values).concat(")");
			System.out.println(statement);
			return statement;			
           }

		
		public String putNewRow(String tablename)
		{
		//First of all we need to get all the neccessary table information back.
			try 
			{
				String key = "";
				Statement stmt = con.createStatement();
				String st = generateInsertStatements(tablename);  //generate the insert statement.
				
				int rows = stmt.executeUpdate(st,Statement.RETURN_GENERATED_KEYS);  //tell the database to return the auto generated Keys.
				
				ResultSet rs = stmt.getGeneratedKeys();
				if (rs.next()) 
				{
				         ResultSetMetaData rsmd = rs.getMetaData();
				         int colCount = rsmd.getColumnCount();
				         do{
				             for (int i = 1; i <= colCount; i++) 
				             {
				                 key = rs.getString(i);
				                 
				             }
				        }while (rs.next());
				         return key;
				} 
				else 
				{
				         System.out.println("There are no generated keys.");
				}
			} 
			catch (Exception e) 
			{
				e.printStackTrace();
			}
			return null;
		}
		
		
		public String getDataType(int TypeID)
		{
		  String dataType;
           switch (TypeID) 
            {
            case -7:  dataType = "BIT";
                     break;
            case -6:  dataType = "TINYINT";
                     break;
            case 5:  dataType = "SMALLINT";
                     break;
            case 4:  dataType = "INTEGER";
                     break;
            case -5:  dataType = "BIGINT";
                     break;
            case 6:  dataType= "FLOAT";
                     break;
            case 7:  dataType = "REAL";
                     break;
            case 8:  dataType = "DOUBLE";
                     break;
            case 2:  dataType = "NUMERIC";
                     break;
            case 3: dataType = "DECIMAL";
                     break;
            case 1: dataType = "CHAR";
                     break;
            case 12: dataType = "VARCHAR";
                     break;
            case -1: dataType = "LONGVARCHAR";
            break;
            case 91: dataType = "DATE";
            break;
            case 92: dataType = "TIME";
            break;
            case 93: dataType = "TIMESTAMP";
            break;
            case -2: dataType = "BINARY";
            break;
            case -3: dataType = "VARBINAY";
            break;
            case -4: dataType = "LONGVARBINARY";
            break;
            case 0: dataType = "NULL";
            break;
            case 1111: dataType = "OTHER";
            break;
            case 2000: dataType = "JAVA_OBJECT";
            break;
            case 2001: dataType = "DISTINCT";
            break;
            case 2002: dataType = "STRUCT";
            break;
            case 2003: dataType = "ARRAY";
            break;
            case 2004: dataType = "BLOB";
            break;
            case 2005: dataType = "CLOB";
            break;
            case 2006: dataType = "REF";
            break;
            case 70: dataType = "DATALINK";
            break;
            case 16: dataType = "BOOLEAN";
            break;
            case -8: dataType = "ROWID";
            break;
            case -15: dataType = "NCHAR";
            break;
            case -9: dataType = "NVARCHAR";
            break;
            case -16: dataType = "LONGVARCHAR";
            break;
            case 2011: dataType = "NCLOB";
            break;
            case 2009: dataType = "SQLXML";
            break;
            default: dataType = "Unknown DataType";
            break;
            }
		return dataType;
		}
        
        public String  getMappedDataTypeDefault(String typeName)
		{
		String dataType;
        String defaultValue;
		if(typeName.equals("BIGINT"))
       {
    	   dataType = "BIGINT";
    	   defaultValue = "0";
    	  
       }
       else if (typeName.equals("BINARY"))
       {
    	   dataType = "CHAR FOR BIT DATA ";
    	   defaultValue = "0";
    	  
       }   
       else if (typeName.equals("BIT1"))
       {
    	   dataType = "CHAR FOR BIT DATA";
    	   defaultValue = "0";
    	  
       }   
       else if (typeName.equals("BLOB"))
       {
    	   dataType = "BLOB (JDBC 2.0 and up)";
    	   defaultValue = "";
    	  
       }   
       else if (typeName.equals("CHAR"))
       {
    	   dataType = "CHAR";
    	   defaultValue = "";
    	  
       }   
       else if (typeName.equals("CLOB"))
       {
    	   dataType = "CLOB (JDBC 2.0 and up)";
    	   defaultValue = "";
    	  
       }   
       else if (typeName.equals("DATE"))
       {
    	   dataType = "DATE";
    	   defaultValue = "";
    	  
       }   
       else if (typeName.equals("DECIMAL"))
       {
    	   dataType = "DECIMAL";
    	   defaultValue = "";
    	  
       }   
       else if (typeName.equals("DOUBLE"))
       {
    	   dataType = "DOUBLE PRECISION";
    	   defaultValue = "";
    	  
       }   
       else if (typeName.equals("FLOAT"))
       {
    	   dataType = "DOUBLE PRECISION2";
    	   defaultValue = "";
    	  
       }   
       else if (typeName.equals("INTEGER"))
       {
    	   dataType = "INTEGER";
    	   defaultValue = "";
    	  
       }   
       else if (typeName.equals("LONG VARCHAR FOR BIT DATA"))
       {
    	   dataType = "BIGINT";
    	   defaultValue = "";
    	  
       }   
       else if (typeName.equals("LONG VARCHAR"))
       {
    	   dataType = "BIGINT";
    	   defaultValue = "";
    	  
       }   
       else if (typeName.equals("NULL"))
       {
    	   dataType = "Not a data type; always a value of a particular type";
    	   defaultValue = "";
    	  
       }   
       else if (typeName.equals("NUMERIC"))
       {
    	   dataType = "DECIMAL";
    	   defaultValue = "";
    	  
       }   
       else if (typeName.equals("REAL"))
       {
    	   dataType = "REAL";
    	   defaultValue = "";
    	  
       }   
       else if (typeName.equals("SMALLINT"))
       {
    	   dataType = "SMALLINT";
    	   defaultValue = "";
    	  
       }   
       else if (typeName.equals("SQLXML3"))
       {
    	   dataType = "XML";
    	   defaultValue = "";
    	  
       }   
       else if (typeName.equals("TIME"))
       {
    	   dataType = "TIME";
    	   defaultValue = "";
    	  
       }   
       else if (typeName.equals("TIMESTAMP"))
       {
    	   dataType = "TIMESTAMP";
    	   defaultValue = "";
    	  
       }   
       else if (typeName.equals("VARBINARY"))
       {
    	   dataType = "VARCHAR FOR BIT DATA ";
    	   defaultValue = "";
    	  
       }   
       else if (typeName.equals("VARCHAR"))
       {
    	   dataType = "VARCHAR";
    	   defaultValue = "";
    	  
       }
       else 
       {
       dataType = "Unknown Type";
       defaultValue = "";
       }
		return defaultValue;
	}
        
        
public void  generateUpdateStatement(String type,Object value) throws SQLException
{
 System.out.println(type);
 System.out.println(value);
	
	
	//ResultSet rs  = con.getMetaData().getColumns(getdatabaseName().toUpperCase(), null, type, null);
    //'UPDATE TABLENAME SET COL1 =VALUE1, COL2 = VAL2 etc where PKColumn = PKValue'
    
   // getPrimaryKey(type).get(0);
    
	//So here we may have a PK Model Name of 10_TEST_n_n
   
	//So we have to split the value of the PK using the _Value
    //java.util.List<String> elements;   
    //elements = Arrays.asList(PKValue.split("_")); 
    
//System.out.println(elements.get(0).toString());
    
	
	
	    
	
	
	int i=0;
	//while (rs.next()) 
	//{
	//i++; 
	
	
	
	
	
	 // statement = "UPDATE ".concat(rs.getString(1)).concat(" SET ").concat(columnname).contact(" = ").concat(value).concat(" WHERE ").concat(PKColumn) = value; 
	}
   
		

public void  generateDeleteStatement(String tablename,String value)
{

	
	
	
}



}
        
        

     
    



